Introduction

The purpose of this document is to provide a generic method that will help you find and fix a performance problem in Oracle. Much has been written on the subject of Oracle Performance Tuning, and this document makes no attempts to supplant that material. Instead, this document will attempt to guide you along a process that will diagnose most common Oracle SQL performance problems.

Scope

There are 4 main areas of performance tuning:

• SQL Tuning – Responsibility of the Developer
• Database Tuning – Responsibility of the Database Administrator
• System Tuning – Responsibility of the System Administrator
• Network Tuning – Responsibility of the Network / LAN / WAN Administrator.

In general terms, the impact of poor design follows the same order. Ie. SQL Tuning will deliver the most improvement, followed by Database Tuning, etc. This document deals only with SQL Tuning.

At times SQL Tuning will result in an optimal design, which still delivers sub-optimal performance. On such occasions the problem must be escalated – usually to the DBA – for resolution.

Before you begin

Every Oracle developer should have a copy of the Oracle Online Documentation – a series of books in HTML format forming a complete reference of the RDBMS and other Oracle products. Critical to understanding performance tuning are the manuals:

• Concepts
• Performance Tuning Guide and Reference
• SQL Reference

If you don’t have access to these manuals, see your DBA now. They are distributed on CD-ROM, but the number of times you will use them justifies the 550Mb on your hard disk for a local install. Alternatively, they are available online at http://tahiti.oracle.com